import pdfplumber
import pandas as pd
import numpy as np
from _sqlite3 import connect
import openpyxl
from openpyxl.reader.excel import load_workbook
global tables
import pyodbc
import re
import os
class PDFTableExtractor:
    def __init__(self, pdf_path):
        self.pdf_path = pdf_path
        self.report_name = os.path.basename(pdf_path).replace("：", ":")
        self.JZRQ = os.path.basename(pdf_path)
        self.JZRQ = re.findall(r'\d+', self.JZRQ)[0]
    # 提取表格
    def find_keyword_and_extract_table(self, keyword,IGSDM):
        self.IGSDM = IGSDM
        with pdfplumber.open(self.pdf_path) as pdf:
            for page_num in range(len(pdf.pages)):
                page = pdf.pages[page_num]
                text = page.extract_text()
                if page_num == 0:
                    lines = text.split('\n')
                    self.ZWMC = lines[2].strip()
                    print(self.ZWMC)
                    print(self.report_name)  ##
                ##字段获
                self.chixu = False
                if keyword in text:
                    self.chixu = False
                    print(f'在第 {page_num + 1} 页找到关键词 "{keyword}"。')
                    tables = self.extract_table_from_page(page)
                    excel_file = f'知识产权tables_page_{self.JZRQ}.xlsx'
                    self.save_tables_to_excel(tables, excel_file)
                    if self.chixu == True:
                            page2 = pdf.pages[page_num + 1]
                            print(excel_file)
                            tables2 = self.concat_table2(page2)
                            a = max([len(sub_list) for sub_list in tables if isinstance(sub_list, list)])
                            b = max([len(sub_list) for sub_list in tables2 if isinstance(sub_list, list)])
                            print(f'上一页表格最大列数 {a}，下一页表格最大列数 {b}')
                            if a == b:
                                print('下一页的表格在页首，拼接表格')
                                print(tables2[0][0])
                                if (tables2[0][0] == '' and tables2[0][1] == '' and tables2[0][2] == '' ):
                                   print('极大可能指标！单元格跨页情况')
                                   tables2 = tables2[1:]
                                   if len(tables2)==7:
                                    tables[-1][1:] = ['生产量', '销售量', '库存量', '生产量比上年增减','销售量比上年增减', '库存量比上年增减']
                                   elif len(tables2)==8:
                                       tables[-1][1:] = ['单位','生产量', '销售量', '库存量', '生产量比上年增减',
                                                         '销售量比上年增减', '库存量比上年增减']
                                   tables.extend(tables2)
                            self.save_tables_to_excel(tables, excel_file)
                    return tables
    def extract_table_from_page(self, page):
        tables = page.extract_tables()
        for i, table in enumerate(tables):
            df = pd.DataFrame(table).replace('\n', '', regex=True)
            print(df[1][0])
            if df[1][0] == '本年新增'or df[1][0]=='本期新增':
                tables=table
                break
            else:tables=None
        if tables != None:##还没有变一维的表格判断
            text=page.extract_text()
            lines=text.split('\n')
            last_line=lines[-2].strip()##本页倒数的文字
            last_table=tables[-1]##本表格倒数的文字
            last_table = ' '.join(str(item) for item in last_table if item is not None)
            Frist_line=lines[1].strip() ##本页第一页文字
            Frist_table=tables[0][0]##本表格第一行文字
            Frist_table=' '.join(str(item) for item in Frist_table if item is not None)
            self.chixu = last_line==last_table
            self.first=Frist_line==Frist_table
            for wenzi in last_line:
                if wenzi in last_table:
                    self.chixu = True
                    break
                else:
                    self.chixu = False
            print(self.chixu)
        return tables
    #跨页连接
    def concat_table2(self, page):
        tables2 = page.extract_tables()

        for i, table in enumerate(tables2):
            tables2 = page.extract_tables()
            excel_file = f'tables2_page_{self.JZRQ}.xlsx'
            for i, x in enumerate(tables2):
                if i == 0:
                    a = x
                    self.save_tables_to_excel(a, excel_file=excel_file)
                    return a
    #保存表格
    def save_tables_to_excel(self, tables, excel_file):
        with pd.ExcelWriter(excel_file, engine='xlsxwriter') as writer:
                df = pd.DataFrame(tables).replace('\n', '', regex=True)
                df = df.replace(' ', '')
                print(df.shape)
                # Check if the DataFrame has 8 columns
                # if df.shape[1] == 8:
                #     # Delete the second column (index 1)
                #     df = df.drop(df.columns[1], axis=1)
                sheet_name = f'知识产权'
                df.to_excel(writer, sheet_name=sheet_name, index=False)
                print(f'Table 知识产权 saved to {excel_file} ')
    #h获取代码
    def get_code(self):
        db_name = r'C:\Users\yuankf47185\PycharmProjects\客户端尝试\mydatabase.db'
        con = connect(db_name)
        Product = "SELECT * FROM product_code"
        self.PRODUCT_CODE = pd.read_sql(Product, con)
        ZB = 'SELECT * FROM ZB_CODE'
        self.ZB_CODE = pd.read_sql(ZB, con)
        DQ = 'SELECT * FROM city_code'
        self.DQ_CODE = pd.read_sql(DQ, con)
        BT='SELECT * FROM GG  '
        self.BT=pd.read_sql(BT,con)
        con.close()
    #企业经营
    def xggs(self):
        excel_file_path = f'知识产权tables_page_{self.JZRQ}.xlsx'
        new_excel_file_path = f'知识产权{self.JZRQ}企业经营.xlsx'
        workbook = openpyxl.load_workbook(excel_file_path)
        sheet_names = workbook.sheetnames
        with pd.ExcelWriter(new_excel_file_path, engine='openpyxl') as writer:
            for i, sheet_name in enumerate(sheet_names, start=1):
                print(sheet_names)
                print(sheet_name)
                print('Processing sheet专利')
                yysr = pd.read_excel(excel_file_path, sheet_name=sheet_name).replace(' ','',regex=True)
                yysr = yysr.drop(0)
                yysr = yysr.drop(1)

                new_columns= ['SJLMYMC','新增知识产权申请数量','累计知识产权申请数量','新增知识产权有效数量','累计知识产权有效数量']
                yysr = yysr.rename(columns=dict(zip(yysr.columns, new_columns)))
                yysr = pd.melt(yysr,
                               id_vars='SJLMYMC',
                               value_vars=list(yysr.columns)[1:],
                               var_name='ZBMC',
                               value_name='ZBSJ')
                yysr['EPBH']=None
                yysr['SJLMYMC']=yysr['SJLMYMC'].replace('合计','产品合计')
                yysr['XXLY'] = None
                yysr['JZRQ'] = None
                yysr['ZTYSMC'] = None
                yysr['SJLMY'] = None
                yysr['ZBDW'] = None
                yysr['SJLME'] = None
                yysr['SJLMS'] = None
                yysr['TJKJ'] = None
                yysr['JZRQ']=None
                yysr['TJQJ']=None
                yysr['JYYWLXDM']=None
                yysr['SJLMY'].iloc[:len(yysr)]='产品'
                yysr['EPBH'].iloc[:len(yysr)] = str(self.EPBH)
                yysr['XXLY'].iloc[:len(yysr)] = str(self.report_name).replace('.pdf', '')
                if '半年度' in yysr['XXLY'].values:
                    yysr.loc[yysr['XXLY'].str.contains('半年度'), 'JZRQ'] = self.JZRQ + '-06-30'
                else:
                    yysr['JZRQ'] = self.JZRQ + '-12-31'
                yysr['XXFBRQ']= yysr.merge(self.BT, on='XXLY', how='left')['XXFBRQ']
                KJ = [
                    yysr['ZBMC']==('新增知识产权申请数量'),
                    yysr['ZBMC']==('累计知识产权申请数量'),
                    yysr['ZBMC']==('新增知识产权有效数量'),
                    yysr['ZBMC']==('累计知识产权有效数量'),
                ]
                KJ_VALUES=['累计值','期末值','累计值','期末值']
                yysr['TJKJ'] = np.select(KJ, KJ_VALUES)
                yysr['TJQJ']='半年'
                yysr['JYYWLXDM'].iloc[:len(yysr)] = '技术研发'
                yysr['ZBDW'].iloc[:len(yysr)] = '个/项'
                yysr['SJLMYDM'] = yysr.merge(self.PRODUCT_CODE, on='SJLMYMC', how='left')['标准代码']
                yysr['ZBDM'] = yysr.merge(self.ZB_CODE, on='ZBMC', how='left')['ZBDM']
                yysr = yysr.reindex(
                    columns=['EPBH', 'XXLY', 'XXFBRQ', 'JZRQ', 'JYYWLXDM', 'SJLMY', 'SJLMYMC', 'SJLMYDM',
                             'SJLME', 'SJLMEMC', 'SJLMEDM', 'SJLMS', 'SJLMSMC', 'ZTYSMC', 'ZBDM', 'ZBMC', 'ZBSJ',
                             'ZBDW', 'TJKJ', 'TJQJ'])
                yysr.to_excel(writer, index=False, sheet_name=sheet_name)
                return new_excel_file_path
    #获取EPBH
    def get_EPBH(self):
        server = 'SHPUBSRV02'
        database = 'JYCFI'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        query = """
                SELECT DISTINCT EPBH
                FROM [10.106.22.51].JYPRIME.dbo.usrQYMB GG 
                WHERE QYBH= CAST(? AS NVARCHAR)
                    """
        IGSDM = self.IGSDM
        self.EPBH = pd.read_sql(query, conn, params=[IGSDM])
        try:
            self.EPBH = self.EPBH['EPBH'][0]
        except:
            self.EPBH = 'EP未匹配成功，需要人工处理'
        return self.EPBH
def run_all(input_folder, IGSDM):
        all_merged_data = pd.DataFrame()
        for pdf_file in os.listdir(input_folder):
            if pdf_file.endswith('.pdf'):
                pdf_path = os.path.join(input_folder, pdf_file)
                # Process each PDF file
                pdf_table_extractor = PDFTableExtractor(pdf_path)
                pdf_table_extractor.get_code()
                keyword_to_find = '申请数（个）'
                tables = pdf_table_extractor.find_keyword_and_extract_table(keyword_to_find, IGSDM)
                pdf_table_extractor.get_EPBH()
                result_merged=pdf_table_extractor.xggs()
                current_merged_data = pd.read_excel(result_merged)
                all_merged_data = pd.concat([all_merged_data, current_merged_data], ignore_index=True)
                EPBH = pdf_table_extractor.get_EPBH()
                output_excel_path_all = f'知识产权EPBH_{EPBH}.xlsx'
        if tables != None:
                all_merged_data.to_excel(output_excel_path_all, index=False, sheet_name='拼接_all')
        else:
                print(f'{pdf_file}年报,未找到关键字')


def run_all(input_folder, IGSDM):
    all_merged_data = pd.DataFrame()
    for pdf_file in os.listdir(input_folder):
        if pdf_file.endswith('.pdf'):
            pdf_path = os.path.join(input_folder, pdf_file)
            # Process each PDF file
            pdf_table_extractor = PDFTableExtractor(pdf_path)
            pdf_table_extractor.get_code()
            keyword_to_find = '申请数（个）'
            tables = pdf_table_extractor.find_keyword_and_extract_table(keyword_to_find, IGSDM)
            pdf_table_extractor.get_EPBH()
            print(type(tables))
            if tables != None:
                result_merged = pdf_table_extractor.xggs()
                current_merged_data = pd.read_excel(result_merged)
                all_merged_data = pd.concat([all_merged_data, current_merged_data], ignore_index=True)
                EPBH = pdf_table_extractor.get_EPBH()
                output_excel_path_all = f'知识产权EPBH_{EPBH}.xlsx'
            else:
                print(f'{pdf_file}年报,未找到关键字')
    if tables != None:
        all_merged_data.to_excel(output_excel_path_all, index=False, sheet_name='拼接_all')
    else:
        print(f'{pdf_file}年报,未找到关键字')


if __name__ == '__main__':
    input_folder = '../downloaded_files/371399'
    IGSDM='379950'
    run_all(input_folder,IGSDM)



